package com.ruoyi.StarStudio.Mapper;

import com.ruoyi.StarStudio.Domain.Soil;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
@SuppressWarnings("all")
public interface SoilMapper {

    //查询全部土壤信息
    @Select("select * from soil")
    public List<Soil> getAll();

    List<Soil> getList(Soil soil);

    @Select("select * from soil where soil_id = #{soil_id}")
    public Soil getBySoilId(long soil_id);

    //根据地块id查询全部土壤信息
    @Select("select s.* from soil s, zone_soil zs where s.soil_id = zs.soil_id and zs.zone_id = #{zone_id}")
    public List<Soil> getAllByZoneId(Integer zone_id);

    //根据地块id查询最新土壤信息
//    @Select("SELECT * FROM (SELECT s.* FROM soil s, zone_soil zs WHERE s.soil_id = zs.soil_id AND zs.zone_id = #{zone_id}) a ORDER BY soil_id  DESC LIMIT 1")
//    public Soil getNewOneByZoneId(Integer zone_id);


    @Select("select soil_id from soil limit 17280")
    public List<Long> findAutoDeleteSoilIds();

    @Select("select max(soil_id) from zone_soil where zone_id = #{zone_id}")
    public int findMaxSoilIdFromZone_Soil(int zone_id);

    //增加土壤信息
    @Insert("insert into soil values (null,#{soil_temperature}, #{soil_moisture}, #{soil_ph}, #{soil_updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "soil_id", keyColumn = "soil_id")  //设置主键自增，指定主键对应的实体类属性名和数据库字段名
    public int add(Soil soil);

    //增加土壤对应地块信息至中间表
    @Insert("insert into zone_soil values(#{zone_id},#{soil_id})")
    public void addToZone_Soil(@Param("zone_id") Integer zone_id, @Param("soil_id") long soil_id);

    //修改土壤信息
    @Update("update soil set soil_temperature=#{soil_temperature},soil_moisture=#{soil_moisture},soil_ph=#{soil_ph},soil_updateTime=#{soil_updateTime} where soil_id = #{soil_id}")
    public void update(Soil soil);

    //根据土壤id删除土壤信息
    @Delete("delete from soil where soil_id = #{soil_id}")
    public void delete(long soil_id);

    //根据土壤id删除地块-土壤中间表项
    @Delete("delete from zone_soil where soil_id = #{soil_id}")
    public void deleteFromZone_Soil(long soil_id);

}
